設計：在不斷創建和刪除表時運行 pg_dump (Design: running pg_dump when tables are continuously created and dropped)

問題描述

設計：在不斷創建和刪除表時運行 pg_dump (Design: running pg_dump when tables are continuously created and dropped)

我們在 Kappa 架構：

• 計算作業的每個實例都會創建並填充自己的結果表，例如“t_jobResult_instanceId”。
• 一旦作業完成，其輸出表就可供訪問。同一作業類型的多個結果表可能同時使用。
• 當不需要輸出表時，將其刪除。

計算結果不是唯一的這個數據庫實例中的表，我們需要定期進行熱備份。這就是我們的問題。當表來來去去時，pg_dump 會死掉。這裡' 一個重現我們的故障模式的簡單測試（它涉及 2 個會話，S1 和 S2）：

S1 : psql ‑U postgres ‑d myuser

create table t1 ( a int );
begin transaction;
drop table t1;

S2 : pg_dump ‑Fc ‑v ‑U postgres ‑d myuser ‑f /tmp/rs.dump

S1 : commit;

Session S2 now shows the following error:

pg_dump ‑Fc ‑U postgres ‑d myuser ‑f /tmp/rs.dump
pg_dump: [archiver (db)] query failed: ERROR: relation "public.t1" does not exist
pg_dump: [archiver (db)] query was: LOCK TABLE public.t1 IN ACCESS SHARE MODE

我們想到了幾個解決方案，但我們都不喜歡其中任何一個：

1. 將所有結果表放入單獨的架構中，並從備份中排除該架構。我們喜歡簡單，但這種方法破壞了模塊化：我們的數據庫對象按垂直切片分組到模式中。
2. 編寫在備份期間暫停錶刪除的應用程序代碼。我們想知道是否有更簡單的解決方案。

我們喜歡以下想法，但無法實現：

1. 我們的結果表遵循命名約定。我們可以編寫一個正則表達式來確定一個表名是否引用了一個結果表。理想情況下，我們可以運行 pg_dump 並使用參數指示它跳過與此模式匹配的表（請注意，在備份開始時選擇要排除的表還不夠好，因為可能會在 pg_dump 運行時創建和刪除新的結果表）。這要么是不可能的，要么是我們不夠聰明，無法弄清楚如何做到這一點。

對不起，背景囉嗦了，但現在我終於想到了這個問題：

• 有沒有辦法實現我們錯過的 3.？
• 有更好的想法嗎？

對不起，背景囉嗦了，但現在我終於想到了這個問題：

• 有沒有辦法實現我們錯過的 3.？
• 有更好的想法嗎？

對不起，背景囉嗦了，但現在我終於想到了這個問題：

• 有沒有辦法實現我們錯過的 3.？
• 有更好的想法嗎？

參考解法

方法 1:

That should be possible using the ‑T option of pg_dump:

‑T table ‑‑exclude‑table=table    Do not dump any tables matching the table pattern.

The psql documentation has details about these patterns:

Within a pattern, * matches any sequence of characters (including no characters) and ? matches any single character. (This notation is comparable to Unix shell file name patterns.) For example, \dt int* displays tables whose names begin with int. But within double quotes, * and ? lose these special meanings and are just matched literally.

A pattern that contains a dot (.) is interpreted as a schema name pattern followed by an object name pattern. For example, \dt foo*.*bar* displays all tables whose table name includes bar that are in schemas whose schema name starts with foo. When no dot appears, then the pattern matches only objects that are visible in the current schema search path. Again, a dot within double quotes loses its special meaning and is matched literally.

Advanced users can use regular‑expression notations such as character classes, for example [0‑9] to match any digit. All regular expression special characters work as specified in Section 9.7.3, except for . which is taken as a separator as mentioned above, * which is translated to the regular‑expression notation .*, ? which is translated to ., and $ which is matched literally. You can emulate these pattern characters at need by writing ? for ., (R+|) for R*, or (R|) for R?. $ is not needed as a regular‑expression character since the pattern must match the whole name, unlike the usual interpretation of regular expressions (in other words, $ is automatically appended to your pattern). Write * at the beginning and/or end if you don't wish the pattern to be anchored. Note that within double quotes, all regular expression special characters lose their special meanings and are matched literally.

(by donkey、Laurenz Albe)

參考文件

1. Design: running pg_dump when tables are continuously created and dropped (CC BY‑SA 2.5/3.0/4.0)